﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Simhopp.Model;

namespace Simhopp
{
    namespace Database
    {
        /// <summary>
        /// Handles the communication to MSSQL database
        /// </summary>
        public class DBConnect:IDisposable
        {
            public DBConnect()
            { }
            
            private SqlConnection OpenSqlConnection()
            {
                string ComputerName = System.Windows.Forms.SystemInformation.ComputerName;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString =	
				//"Data Source=(LocalDB)\\v11.0;AttachDbFilename=C:\\Users\\SPK10nguyen\\Documents\\Visual Studio 2013\\Projects\\SimhoppDeluxeOfDoom\\Database\\SimhoppDeluxeOfDoom.mdf;Integrated Security=True;Connect Timeout=30";
				"Data Source=" + ComputerName + ";Initial Catalog=SimhoppDeluxeOfDoom;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
                conn.Open();
                return conn;
            }
			#region Competition
			public int AddCompetition(string Name, CompetitionType Event, string Location, 
                DateTime Date, Height Height, AgeClass AgeClass, char Gender, 
                int JudgingJudges, int RoundsInCompetition)
            {
                int ID = 0;
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("AddCompetition", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@Name", Name));
                command.Parameters.Add(new SqlParameter("@Event", Event));
                command.Parameters.Add(new SqlParameter("@Location", Location));
                command.Parameters.Add(new SqlParameter("@Date", Date));
                command.Parameters.Add(new SqlParameter("@Height", Height));
                command.Parameters.Add(new SqlParameter("@AgeClass", AgeClass));
                command.Parameters.Add(new SqlParameter("@Gender", Gender));
                command.Parameters.Add(new SqlParameter("@JudgingJudges", JudgingJudges));
                command.Parameters.Add(new SqlParameter("@RoundsInCompetition", RoundsInCompetition));
                SqlParameter retValue = command.Parameters.Add("return", SqlDbType.Int);
                retValue.Direction = ParameterDirection.ReturnValue;
                command.ExecuteScalar();

                conn.Close();

                ID = (int)retValue.Value;

                return ID;
            }
            
            public void UpdateCompetition(int CompetitionID, string Name, 
                CompetitionType Event, string Location, DateTime Date, Height Height, 
                AgeClass AgeClass, char Gender, int JudgingJudges, int RoundsInCompetition)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("UpdateCompetition", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", CompetitionID));
                command.Parameters.Add(new SqlParameter("@Name", Name));
                command.Parameters.Add(new SqlParameter("@Event", Event));
                command.Parameters.Add(new SqlParameter("@Location", Location));
                command.Parameters.Add(new SqlParameter("@Date", Date));
                command.Parameters.Add(new SqlParameter("@Height", Height));
                command.Parameters.Add(new SqlParameter("@AgeClass", AgeClass));
                command.Parameters.Add(new SqlParameter("@Gender", Gender));
                command.Parameters.Add(new SqlParameter("@JudgingJudges", JudgingJudges));
                command.Parameters.Add(new SqlParameter("@RoundsInCompetition", RoundsInCompetition));
                command.ExecuteNonQuery();

                conn.Close();
            }
            
            public void RemoveCompetition(int CompetitionID)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("RemoveCompetition", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", CompetitionID));
                command.ExecuteNonQuery();

                conn.Close();
            }

            public Competition GetCompetitionWithID(int CompetitionID)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("GetCompetitionWithID", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@ID", CompetitionID));
                SqlDataReader sdr = command.ExecuteReader();
                
                if (sdr.HasRows)
                {
                    sdr.Read();
                    int CompID = Convert.ToInt32(sdr["ID"].ToString());
                    string Name = sdr["Name"].ToString();
                    CompetitionType Event = (CompetitionType)Enum.Parse(typeof(CompetitionType), sdr["Event"].ToString());
                    string Location = sdr["Location"].ToString();
                    DateTime Date = Convert.ToDateTime(sdr["Date"]);
                    Height Height = (Height)Enum.Parse(typeof(Height), sdr["Height"].ToString());
                    AgeClass AgeClass = (AgeClass)Enum.Parse(typeof(AgeClass), sdr["AgeClass"].ToString());
                    char Gender = Convert.ToChar(sdr["Gender"]);
                    int JudgingJudges = Convert.ToInt32(sdr["JudgingJudges"]);
                    int RoundsInCompetition = Convert.ToInt32(sdr["RoundsInCompetition"]);
                    Competition comp = new Competition(CompID, Name, Event, Location,
                        Date, Height, AgeClass, Gender, JudgingJudges,
                        RoundsInCompetition);
                    conn.Close();
                    return comp;
                }
                else
                {
                    conn.Close();
                    return null;
                }
            }

            public List<Competition> GetAllCompetition(List<Competition> CompList)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("GetAllCompetition", conn);
                command.CommandType = CommandType.StoredProcedure;
                SqlDataReader sdr = command.ExecuteReader();
                if( sdr.HasRows )
                {
                    while( sdr.Read() )
                    {
                        int CompID = Convert.ToInt32(sdr["ID"].ToString());
                        string Name = sdr["Name"].ToString();
                        CompetitionType Event = (CompetitionType)Enum.Parse(typeof(CompetitionType), sdr["Event"].ToString());
                        string Location = sdr["Location"].ToString();
                        DateTime Date = Convert.ToDateTime(sdr["Date"]);
                        Height Height = (Height)Enum.Parse(typeof(Height), sdr["Height"].ToString());
                        AgeClass AgeClass = (AgeClass)Enum.Parse(typeof(AgeClass), sdr["AgeClass"].ToString());
                        char Gender = Convert.ToChar(sdr["Gender"]);
                        int JudgingJudges = Convert.ToInt32(sdr["JudgingJudges"]);
                        int RoundsInCompetition = Convert.ToInt32(sdr["RoundsInCompetition"]);
                        CompList.Add(new Competition(CompID, Name, Event, Location,
                        Date, Height, AgeClass, Gender, JudgingJudges,
                        RoundsInCompetition));
                    }
                }
                conn.Close();
                return CompList;
            }
			#endregion
			#region Judge
			public int AddJudge(string Name, Countries Nationality, 
                string Password, string Username, int CompetitionID)
            {
                int ID;
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("AddJudge", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@Name", Name));
                command.Parameters.Add(new SqlParameter("@Nationality", Nationality));
                command.Parameters.Add(new SqlParameter("@Password", Password));
                command.Parameters.Add(new SqlParameter("@Username", Username));
                command.Parameters.Add(new SqlParameter("@CompetitionID", CompetitionID));
                SqlParameter retValue = command.Parameters.Add("return", SqlDbType.Int);
                retValue.Direction = ParameterDirection.ReturnValue;
                command.ExecuteScalar();

                conn.Close();

                ID = (int)retValue.Value;

                return ID;
            }

            public void AddJudgeToCompetition(int JudgeID, int CompetitionID)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("AddJudgeToJudgeList", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@CompetitionID", CompetitionID));
                command.Parameters.Add(new SqlParameter("@JudgeID", JudgeID));
                command.ExecuteNonQuery();

                conn.Close();
            }

            public void GetJudgesWithCompetitionID(int CompetitionID, List<Judge> JudgeList)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("GetJudgesWithCompetitionID", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@CompetitionID", CompetitionID));
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr.HasRows)
                {
                    while (sdr.Read())
                    {
                        int JudgeID = Convert.ToInt32(sdr["ID"].ToString());
                        string Name = sdr["Name"].ToString();
                        Countries Nationality = (Countries)Enum.Parse(typeof(Countries), sdr["Nationality"].ToString());
                        //Colors colorValue = (Colors)Enum.Parse(typeof(Colors), colorString);
                        string Password = sdr["Password"].ToString();
                        string Username = sdr["Username"].ToString();
                        JudgeList.Add(new Judge(JudgeID, Name, Nationality, Password, Username));
                    }

                }
                conn.Close();

            }

            public void UpdateJudge(int JudgeID, string Name, Countries Nationality, string Password, string Username, int CompetitionID)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("UpdateJudge", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", JudgeID));
                command.Parameters.Add(new SqlParameter("@Name", Name));
                command.Parameters.Add(new SqlParameter("@Nationality", Nationality));
                command.Parameters.Add(new SqlParameter("@Password", Password));
                command.Parameters.Add(new SqlParameter("@Username", Username));
                command.Parameters.Add(new SqlParameter("@CompetitionID", CompetitionID));

                command.ExecuteNonQuery();

                conn.Close();
            }

            public Judge GetJudgeWithID(int JudgeID)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("GetJudgeWithID", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", JudgeID));
                SqlDataReader sdr = command.ExecuteReader();
                if(sdr.HasRows)
                {
                    sdr.Read();
                    int ID = Convert.ToInt32(sdr["ID"]);
                    string Name = sdr["Name"].ToString();
                    Countries Nationality = (Countries)Enum.Parse(typeof(Countries), sdr["Nationality"].ToString());
                    string Password = sdr["Password"].ToString();
                    string Username = sdr["Username"].ToString();
                    Judge j = new Judge(ID, Name, Nationality, Password, Username);
                    return j;
                }
                return null;
            }

            public void RemoveJudge(int JudgeID)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("RemoveJudge", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", JudgeID));
                command.ExecuteNonQuery();

                conn.Close();
            }

            public void GetAllJudge(List<Judge> JudgeList)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("GetAllJudges", conn);
                command.CommandType = CommandType.StoredProcedure;

                SqlDataReader sdr = command.ExecuteReader();
                if (sdr.HasRows)
                {
                    while (sdr.Read())
                    {
                        int ID = Convert.ToInt32(sdr["ID"]);
                        string Name = sdr["Name"].ToString();
                        Countries Nationality = (Countries)Enum.Parse(typeof(Countries), sdr["Nationality"].ToString());
                        string Password = sdr["Password"].ToString();
                        string Username = sdr["Username"].ToString();
                        JudgeList.Add(new Judge(ID, Name, Nationality, Password, Username));
                    }
                }
            }
			#endregion
			#region Diver
			public int AddDiver(string Name, DateTime DateOfBirth, char Gender, int StartNumber, 
                double Score, Countries Nationality, int CompetitionID)
            {
                int ID;
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("AddDiver", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@Name", Name));
                command.Parameters.Add(new SqlParameter("@Nationality", Nationality));
                command.Parameters.Add(new SqlParameter("@DateOfBirth", DateOfBirth));
                command.Parameters.Add(new SqlParameter("@Gender", Gender));
                command.Parameters.Add(new SqlParameter("@StartNumber", StartNumber));
                command.Parameters.Add(new SqlParameter("@Score", Score));
                command.Parameters.Add(new SqlParameter("@CompetitionID", CompetitionID));
                SqlParameter retValue = command.Parameters.Add("return", SqlDbType.Int);
                retValue.Direction = ParameterDirection.ReturnValue;
                command.ExecuteScalar();

                conn.Close();

                ID = (int)retValue.Value;

                return ID;
            }

            public void AddDiverToCompetition(int DiverID, int CompetitionID, int StartNumber, double TotalPoints)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("AddDiverToDiverList", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@CompetitionID", CompetitionID));
                command.Parameters.Add(new SqlParameter("@DiverID", DiverID));
                command.Parameters.Add(new SqlParameter("@StartNumber", StartNumber));
                command.Parameters.Add(new SqlParameter("@TotalPoints", TotalPoints));
                command.ExecuteNonQuery();

                conn.Close();
            }

            public void GetDiverWithCompetitionID(int CompetitionID, List<Diver> DiverList)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("GetDiversWithCompetitionID", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@CompetitionID", CompetitionID));
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr.HasRows)
                {
                    while (sdr.Read())
                    {
                        int DiverID = Convert.ToInt32(sdr["ID"].ToString());
                        string Name = sdr["Name"].ToString();
                        Countries Nationality = (Countries)Enum.Parse(typeof(Countries), sdr["Nationality"].ToString());
                        DateTime DateOfBirth = Convert.ToDateTime(sdr["DateOfBirth"]);
                        char Gender = Convert.ToChar(sdr["Gender"]);
                        int StartNumber = Convert.ToInt32(sdr["StartNumber"]);
                        double Score = Convert.ToDouble(sdr["TotalPoints"]);
                        //int CompID = Convert.ToInt32(sdr["CompetitionID"]);
                        DiverList.Add(new Diver(DiverID, Name, DateOfBirth, Gender,
                            StartNumber, Score, Nationality));
                    }
                }
                conn.Close();
            }

            public void UpdateDiver(int ID, string Name, DateTime DateOfBirth, char Gender, int StartNumber,
                double Score, Countries Nationality, int CompetitionID)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("UpdateDiver", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", ID));
                command.Parameters.Add(new SqlParameter("@Name", Name));
                command.Parameters.Add(new SqlParameter("@Nationality", Nationality));
                command.Parameters.Add(new SqlParameter("@DateOfBirth", DateOfBirth));
                command.Parameters.Add(new SqlParameter("@Gender", Gender));
                command.Parameters.Add(new SqlParameter("@StartNumber", StartNumber));
                command.Parameters.Add(new SqlParameter("@TotalPoints", Score));
                command.Parameters.Add(new SqlParameter("@CompetitionID", CompetitionID));

                command.ExecuteNonQuery();

                conn.Close();
            }

            public Diver GetDiverWithID(int ID)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("GetDiverWithID", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@ID", ID));
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr.HasRows)
                {
                    sdr.Read();
                    int DiverID = Convert.ToInt32(sdr["ID"].ToString());
                    string Name = sdr["Name"].ToString();
                    Countries Nationality = (Countries)Enum.Parse(typeof(Countries), sdr["Nationality"].ToString());
                    DateTime DateOfBirth = Convert.ToDateTime(sdr["DateOfBirth"]);
                    char Gender = Convert.ToChar(sdr["Gender"]);
                    int StartNumber = Convert.ToInt32(sdr["StartNumber"]);
                    double Score = Convert.ToDouble(sdr["Score"]);
                    //int CompID = Convert.ToInt32(sdr["CompetitionID"]);
                    Diver d = new Diver(ID, Name, DateOfBirth, Gender, StartNumber, Score,
                        Nationality);
                    return d;
                }
                return null;
            }

            public void GetAllDiver(List<Diver> DiverList)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("GetAllDivers", conn);
                command.CommandType = CommandType.StoredProcedure;
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr.HasRows)
                {
                    while (sdr.Read())
                    {
                        int DiverID = Convert.ToInt32(sdr["ID"].ToString());
                        string Name = sdr["Name"].ToString();
                        Countries Nationality = (Countries)Enum.Parse(typeof(Countries), sdr["Nationality"].ToString());
                        DateTime DateOfBirth = Convert.ToDateTime(sdr["DateOfBirth"]);
                        char Gender = Convert.ToChar(sdr["Gender"]);
                        int StartNumber = Convert.ToInt32(sdr["StartNumber"]);
                        double Score = Convert.ToDouble(sdr["Score"]);
                        //int CompID = Convert.ToInt32(sdr["CompetitionID"]);
                        DiverList.Add(new Diver(DiverID, Name, DateOfBirth, Gender,
                            StartNumber, Score, Nationality));
                    }
                }
            }

            public void RemoveDiver(int ID)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("RemoveDiver", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID", ID));
                command.ExecuteNonQuery();

                conn.Close();
            }

            public void GetDivesForDiver(List<Dive> DiveList, int DiverID)
            {

                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("GetDivesForDiver", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@DiverID", DiverID));
                
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr.HasRows)
                {
                    while (sdr.Read())
                    {
                        int DiveID = Convert.ToInt32(sdr["DiveID"]);
                        string Name = sdr["Name"].ToString();
                        string NameCode = sdr["NameCode"].ToString();
                        double Difficulty = Convert.ToDouble(sdr["Difficulty"]);
                        double Score = Convert.ToDouble(sdr["Score"]);
                        int DiveNumber = Convert.ToInt32(sdr["DiveNumber"]);
                        //int CompID = Convert.ToInt32(sdr["CompetitionID"]);
                        DiveList.Add(new Dive(DiveID, 0, 0, Name, Difficulty, NameCode, DiveNumber));
                    }
                }
            }

            public void AddDiveToDiver(int DiveID, int DiverID, int DiveNumber, double DiveScore)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("AddDiveToDiveList", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@DiverID", DiverID));
                command.Parameters.Add(new SqlParameter("@DiveID", DiveID));
                command.Parameters.Add(new SqlParameter("@Score", DiveScore));
                command.Parameters.Add(new SqlParameter("@DiveNumber", DiveNumber));
                command.ExecuteNonQuery();

                conn.Close();
            }

            public void GetAllDives(List<Dive> DiveList)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("GetAllDives", conn);
                command.CommandType = CommandType.StoredProcedure;
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr.HasRows)
                {
                    while (sdr.Read())
                    {
                        int DiveID = Convert.ToInt32(sdr["ID"].ToString());
                        string Name = sdr["Name"].ToString();
                        double Difficulty = Convert.ToDouble(sdr["Difficulty"]);
                        string NameCode = sdr["NameCode"].ToString();
                        DiveList.Add(new Dive(DiveID, 0,0,Name,Difficulty,NameCode,
                            0));
                    }

                }
            }

            public void RemoveDiveFromDiveList(int DiverID, int DiveID)
            {
                SqlConnection conn = OpenSqlConnection();
                SqlCommand command = new SqlCommand("RemoveDiveFromDiveList", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@DiverID", DiverID));
                command.Parameters.Add(new SqlParameter("@DiveID", DiveID));
                command.ExecuteNonQuery();

                conn.Close();
            }

            public void UpdateDiveInDiveList(int DiverID, int DiveID, int DiveNumber, double Score)
            {
                SqlConnection conn = OpenSqlConnection();

                SqlCommand command = new SqlCommand("UpdateDiveInDiveList", conn);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@DiverID", DiverID));
                command.Parameters.Add(new SqlParameter("@DiveID", DiveID));
                command.Parameters.Add(new SqlParameter("@DiveNumber", DiveNumber));
                command.Parameters.Add(new SqlParameter("@Score", Score));

                command.ExecuteNonQuery();

                conn.Close();
            }
			#endregion
			public Judge JudgeLogin(string username,string password)
			{
				SqlConnection connect = OpenSqlConnection();
				SqlCommand command = new SqlCommand("GetJudgeLogin", connect);
				string query = "";
				Judge rJudge = new Judge() ;

				command.CommandType = CommandType.StoredProcedure;
				command.Parameters.Add(new SqlParameter("@Username", username));
				command.Parameters.Add(new SqlParameter("@Password", password));

				SqlDataReader dr = command.ExecuteReader();
				if(dr.HasRows)
				{
					dr.Read();
					query = dr["Prim"].ToString();
					rJudge.ID = int.Parse(query);
					rJudge.Username = username;
					rJudge.Password = password;
					rJudge.Nationality = Countries.Sverige;
					rJudge.Name = dr["Name"].ToString();
					return rJudge;
				}
				return null;
			}
            public void Dispose()
            {

            }

        }
    }
}
